﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using GTCAS.DataAccess.Common;
using GTCAS.CommonLib.Entities;


namespace GTCAS.DataAccess
{
    /// <summary>
    /// Chứa các Methods thao tác với Danh mục tỉnh thành phố
    /// </summary>
    public class DM_TinhThanhDAO
    {

        

        /// <summary>
        /// Hàm tạo
        /// </summary>
        public DM_TinhThanhDAO()
        {
        }

        /// <summary>
        /// Lấy chuỗi kết nối tới DB tương ứng với tỉnh thành phố
        /// </summary>
        /// <param name="id_tinhthanh"></param>
        /// <returns></returns>
        public string GetConnectionStringByID_TinhThanh(int id_tinhthanh)
        {
            string result = "";
            BaseDAO adapter = new BaseDAO(DbConfig.DefaulConnectionString);
            adapter.AddParameter("id_tinhthanh", id_tinhthanh);
            OracleParameter pReturn = new OracleParameter();
            pReturn.OracleType = OracleType.VarChar;
            pReturn.Direction = ParameterDirection.ReturnValue;
            adapter.AddParameter(pReturn);


            try
            {
                adapter.ExecuteNonQuery(DbConfig.DefaultShemma + DbConstants.Functions.FUNC_Get_ConnectionStr_By_Id_TinhThanh, CommandType.StoredProcedure, CustomConnectionState.CloseOnExit).ToString();
                result = pReturn.Value.ToString();
            }
            catch (Exception exc)
            {//Custom Error here

                throw exc;

            }
            finally { adapter.Dispose(); }
            return result;

        }

        /// <summary>
        /// Lấy Schema tương ứng với tỉnh thành phố
        /// </summary>
        /// <param name="id_tinhthanh"></param>
        /// <returns></returns>
        public string GetSchemaByID_TinhThanh(int id_tinhthanh)
        {
            string result = "";
            BaseDAO adapter = new BaseDAO(DbConfig.DefaulConnectionString);
            adapter.AddParameter("id_tinhthanh", id_tinhthanh);
            OracleParameter pReturn = new OracleParameter();
            pReturn.OracleType = OracleType.VarChar;
            pReturn.Direction = ParameterDirection.ReturnValue;
            pReturn.Size = 500;
            adapter.AddParameter(pReturn);


            try
            {

                adapter.ExecuteNonQuery(DbConfig.DefaultShemma + DbConstants.Functions.FUNC_Get_Schema_By_Id_TinhThanh, CommandType.StoredProcedure, CustomConnectionState.KeepOpen);
                result = pReturn.Value.ToString();
            }
            catch (Exception exc)
            {//Custom Error here

                throw exc;

            }
            finally { adapter.Dispose(); }
            return result;

        }
        /// <summary>
        /// Lấy danh sách tất cả tỉnh thành
        /// </summary>
        /// <returns></returns>
        public List<DM_TinhThanh> GetAll()
        {
            
            List<DM_TinhThanh> result = new List<DM_TinhThanh>();
            BaseDAO adapter = new BaseDAO(DbConfig.DefaulConnectionString);
            try
            {

                result = adapter.GetList<DM_TinhThanh>("SELECT * FROM " + DbConfig.DefaultShemma + ".DM_TINHTHANH", CommandType.Text, CustomConnectionState.CloseOnExit);
            }
            catch (Exception exc)
            {//Custom Error here

                throw exc;

            }
            finally { adapter.Dispose(); }
            return result;
        }
        public DM_TinhThanh GetById(long id_tinhthanh)
        {
            DM_TinhThanh result = null;
            BaseDAO adapter = new BaseDAO(DbConfig.DefaulConnectionString);
            adapter.AddParameter(":id_tinhthanh",id_tinhthanh);
            try
            {
                string command = "SELECT * FROM " + DbConfig.DefaultShemma + ".DM_TINHTHANH WHERE ID_TINHTHANH=:id_tinhthanh ";
                var ret = adapter.GetList<DM_TinhThanh>(command, CommandType.Text, CustomConnectionState.CloseOnExit);
                result = ret.FirstOrDefault();
            }
            catch (Exception exc)
            {//Custom Error here

                throw exc;

            }
            finally { adapter.Dispose(); }
            return result;
        }


    }
}
